package platform_api.Repository;

import platform_api.Model.TradeData;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface TradeDataRepository extends CrudRepository<TradeData, Integer> {

    @Query(value = "select * from trade_data where variety = :variety and transaction_date between :startTime and :endTime", nativeQuery = true)
    List<TradeData> getByVarietyAndTransactionDate(@Param("startTime") Date startTime, @Param("endTime") Date endTime, @Param("variety") String variety);

    /**
     * 获取最小值
     *
     * @param variety   品种代码
     * @param column    字段
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return Object
     */
    @Query(value = "select\n" +
            "CASE :col\n" +
            "    WHEN 'settlement_yesterday' THEN min(settlement_yesterday)\n" +
            "    WHEN 'opening_today'        THEN min(opening_today)\n" +
            "    WHEN 'highest_price'        THEN min(highest_price)\n" +
            "    WHEN 'lowest_price'         THEN min(lowest_price)\n" +
            "    WHEN 'closing_today'        THEN min(closing_today)\n" +
            "    WHEN 'settlement_today'     THEN min(settlement_today)\n" +
            "    WHEN 'up_and_down1'         THEN min(up_and_down1)\n" +
            "    WHEN 'up_and_down2'         THEN min(up_and_down2)\n" +
            "    WHEN 'volume'               THEN min(volume)\n" +
            "    WHEN 'empty_quantity'       THEN min(empty_quantity)\n" +
            "    WHEN 'increase_or_decrease' THEN min(increase_or_decrease)\n" +
            "    WHEN 'turn_over'            THEN min(turn_over)\n" +
            "    WHEN 'delivery_settlement_price' THEN min(delivery_settlement_price)\n" +
            "    else min(highest_price)\n" +
            "END as result\n" +
            "FROM trade_data where " +
            "variety = :variety and  " +
            "transaction_date between :startTime and :endTime", nativeQuery = true)
    Object getMinValue(@Param("variety") String variety, @Param("col") String column, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    /**
     * 获取最小值
     *
     * @param variety   品种代码
     * @param column    字段
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return Object
     */
    @Query(value = "select\n" +
            "CASE :col\n" +
            "    WHEN 'settlement_yesterday' THEN max(settlement_yesterday)\n" +
            "    WHEN 'opening_today'        THEN max(opening_today)\n" +
            "    WHEN 'highest_price'        THEN max(highest_price)\n" +
            "    WHEN 'lowest_price'         THEN max(lowest_price)\n" +
            "    WHEN 'closing_today'        THEN max(closing_today)\n" +
            "    WHEN 'settlement_today'     THEN max(settlement_today)\n" +
            "    WHEN 'up_and_down1'         THEN max(up_and_down1)\n" +
            "    WHEN 'up_and_down2'         THEN max(up_and_down2)\n" +
            "    WHEN 'volume'               THEN max(volume)\n" +
            "    WHEN 'empty_quantity'       THEN max(empty_quantity)\n" +
            "    WHEN 'increase_or_decrease' THEN max(increase_or_decrease)\n" +
            "    WHEN 'turn_over'            THEN max(turn_over)\n" +
            "    WHEN 'delivery_settlement_price' THEN max(delivery_settlement_price)\n" +
            "    else max(highest_price)\n" +
            "END as result\n" +
            "FROM trade_data where " +
            "variety = :variety and  " +
            "transaction_date between :startTime and :endTime", nativeQuery = true)
    Object getMaxValue(@Param("variety") String variety, @Param("col") String column, @Param("startTime") Date startTime, @Param("endTime") Date endTime);


    /**
     * 获取一段时间内均值
     *
     * @param variety   品种代码
     * @param column    字段
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return Object
     */
    @Query(value = "select\n" +
            "CASE :col\n" +
            "    WHEN 'settlement_yesterday' THEN avg(settlement_yesterday)\n" +
            "    WHEN 'opening_today'        THEN avg(opening_today)\n" +
            "    WHEN 'highest_price'        THEN avg(highest_price)\n" +
            "    WHEN 'lowest_price'         THEN avg(lowest_price)\n" +
            "    WHEN 'closing_today'        THEN avg(closing_today)\n" +
            "    WHEN 'settlement_today'     THEN avg(settlement_today)\n" +
            "    WHEN 'up_and_down1'         THEN avg(up_and_down1)\n" +
            "    WHEN 'up_and_down2'         THEN avg(up_and_down2)\n" +
            "    WHEN 'volume'               THEN avg(volume)\n" +
            "    WHEN 'empty_quantity'       THEN avg(empty_quantity)\n" +
            "    WHEN 'increase_or_decrease' THEN avg(increase_or_decrease)\n" +
            "    WHEN 'turn_over'            THEN avg(turn_over)\n" +
            "    WHEN 'delivery_settlement_price' THEN avg(delivery_settlement_price)\n" +
            "    else avg(highest_price)\n" +
            "END as result\n" +
            "FROM trade_data where " +
            "variety = :variety and  " +
            "transaction_date between :startTime and :endTime", nativeQuery = true)
    Object getMeanOfSingleColumn(@Param("variety") String variety, @Param("col") String column, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query(value = "select variety from trade_data group By variety", nativeQuery = true)
    List<String> getVarieties();
}